Introduction to SQL

Tags: #◇Code Snippets #◇CCINFOM #◇SQL #◇DDL #◇DML #◇Database #◇M3

◇Overview

Structured Query Language (SQL) serves as the universal vernacular for interacting with Relational Database Management Systems (RDBMS). It is a comprehensive standard that specifies the exact syntax and semantics required to define data structures, manipulate stored information, and exert rigorous control over data access. Understanding SQL is essential for any database practitioner, as it forms the bedrock for communicating with complex data repositories, enabling everything from structural database design to intricate data analysis.

◇Key Definitions

SQL (Structured Query Language): The standardized programming language utilized to manage, query, and design relational databases.
DDL (Data Definition Language): The subset of SQL commands used to define, modify, or destroy the structural framework of a database (e.g., tables, views, indexes).
DML (Data Manipulation Language): The subset of SQL commands concerned with maintaining and retrieving the actual data stored within those structures.
DCL (Data Control Language): The subset of SQL commands used to manage administration privileges and control user access to the database.

◇Theoretical Framework / Core Concepts

SQL Environment Components

The SQL environment is broadly divided into distinct sub-languages based on their functional purpose:

Data Definition Language (DDL): This includes commands like CREATE, ALTER, and DROP. DDL is strictly concerned with the schema—the blueprints of the database. When you execute a DDL command, you are physically changing the structure of the database rather than the data housed within it.
Data Manipulation Language (DML): Comprising commands such as SELECT, INSERT, UPDATE, and DELETE, DML is what users and applications utilize most frequently. It governs the insertion of new records, the modification of existing data, and the execution of queries to retrieve specific datasets.
Data Control Language (DCL): While not the primary focus of initial database modeling, DCL (including GRANT and REVOKE) is critical for security, determining which users have the authority to view or manipulate data.

◇Worked Examples / Code Walkthroughs

Data Definition Language (DDL) in Practice

Creating a Table

When instantiating a new table, one must specify the attributes (columns), assign appropriate data types (like NUMBER or VARCHAR2), define default values, and establish integrity constraints such as Primary Keys.

『』

SQL

CREATE TABLE CUSTOMER_T (
    CUSTOMER_ID NUMBER(11) NOT NULL,
    CUSTOMER_NAME VARCHAR2(50) NOT NULL,
    CUSTOMER_ADDRESS VARCHAR2(100),
    CITY VARCHAR2(50),
    STATE VARCHAR2(2) DEFAULT 'NY',
    POSTAL_CODE VARCHAR2(10),
    CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID)
);

Explanation: This command creates a structured entity named CUSTOMER_T. The CUSTOMER_ID is forced to be non-null and acts as the unique identifier (Primary Key). If a state is not provided upon insertion, it defaults to 'NY'.

Altering and Dropping Tables

As business requirements evolve, so too must the database schema.

『』

SQL

-- Appends a new column named 'TYPE' to the existing table
ALTER TABLE CUSTOMER_T ADD (TYPE VARCHAR(2));

-- Permanently eradicates the table and all its structural metadata
DROP TABLE CUSTOMER_T;

Creating Indexes and Views

Indexes are internal structures that drastically speed up data retrieval times, akin to a book's index. Views are virtual tables generated dynamically from underlying base tables, useful for security (hiding columns) and simplifying complex queries.

『』

SQL

-- Generates an index on the CUSTOMER_NAME column for faster lookups
CREATE INDEX NAME_IDX ON CUSTOMER_T(CUSTOMER_NAME);

-- Creates a virtual table restricting visibility to only high-value products
CREATE VIEW EXPENSIVE_STUFF_V AS
SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE
FROM PRODUCT_T
WHERE UNIT_PRICE > 300
WITH CHECK_OPTION;

Data Manipulation Language (DML) in Practice

Modifying Data: INSERT, UPDATE, DELETE

Data is dynamic; DML allows for seamless modification of this data.

『』

SQL

-- Populates an entire row, mapping values exactly to the column order
INSERT INTO CUSTOMER_T VALUES (1, 'Contemporary Casuals', '1355 S. Himes Blvd.', 'Gainesville', 'FL', '32601');

-- Selectively populates specific columns; unspecified columns will be null or use defaults
INSERT INTO PRODUCT_T (PRODUCT_ID, PRODUCT_DESCRIPTION, STANDARD_PRICE) 
VALUES (1, 'End Table', 175);

-- Modifies an existing record based on a specific condition
UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7;

-- Removes specific records satisfying the WHERE clause
DELETE FROM CUSTOMER_T WHERE STATE = 'HI';

-- Flushes all data from the table while retaining its DDL structure
DELETE FROM CUSTOMER_T;

Querying Data: The SELECT Statement

The SELECT statement is the cornerstone of SQL data retrieval. A fully fleshed-out query leverages several clauses, fundamentally structured as follows:

SELECT: Dictates which specific columns to return.
FROM: Identifies the source tables or views.
WHERE: Applies a row-level filter to the initial dataset.
GROUP BY: Aggregates identical data into distinct groups.
HAVING: Applies a filter to the aggregated groups (similar to WHERE, but for groups).
ORDER BY: Defines the final sorting order (ascending or descending) of the output.

『』

SQL

SELECT STATE, COUNT(STATE) 
FROM CUSTOMER_T
WHERE STATE IN ('FL', 'TX', 'CA', 'HI')
GROUP BY STATE
HAVING COUNT(STATE) > 1
ORDER BY STATE;

Explanation: This query extracts the state names and a tally of customers within those states. It initially filters the raw data to only include four specific states. It then aggregates the counts by state, further restricting the final output to only show those states that have more than one customer, finally organizing the list alphabetically.

[!tip] Processing Order of SQL Statements
Internally, the DBMS does not read a query top-to-bottom. It processes in this strict logical sequence: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Understanding this internal logic is crucial for troubleshooting complex nested queries and aggregate functions.

◇Summary & Key Takeaways

SQL is categorized into DDL (structure), DML (data modification and retrieval), and DCL (security).
DDL commands (CREATE, ALTER, DROP) physically modify the database schema and its metadata.
DML commands (INSERT, UPDATE, DELETE) allow for the manipulation of the records stored within the tables.
The SELECT statement is a powerful DML tool for extracting specific datasets using filtering (WHERE), aggregating (GROUP BY), and sorting (ORDER BY).
To construct accurate and optimized queries, one must understand the logical internal processing order of SQL clauses.

Previous Topic: ◇Database and Its Environment
Next Topic: ◇Advanced SQL
Related Notes: ◇Introduction to Databases

INTRODUCTION TO SQL